package com.vcaresolution.logistic.dao;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.vcaresolution.logistic.pojo.Shippingbill;

/**
 * The <code>ShippingDAO</code> class is a DAO Associated to do various 
 * options for shipping at DAO level
 *
 * @author Kushal Shah
 * @version 1.0
 * @since ShipSoft 1.0
 */
@Repository
public class ShippingDAO extends BaseDAO{

	/**
	 * This method is used to get ShippingBill Details And Containers Details associated with SBID and SHIPPINGCONTAINERID
	 * 
	 * @param sbid
	 * @param containerid
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Transactional
	public Shippingbill getShippingBillDetailsAndContainers(Serializable sbid,Serializable containerid){
		
		List<Shippingbill> shippingBillList = new ArrayList<Shippingbill>();
		StringBuilder queryForSelectData = new StringBuilder();
		
		queryForSelectData.append(" SELECT * FROM SHIPPINGBILL SB ");
		queryForSelectData.append(" INNER JOIN SHIPPINGCONTAINERS SC ON SB.SBID = SC.SBID ");
		queryForSelectData.append(" WHERE SB.SBID = ").append(":sbid").append(" AND SC.SHIPPINGCONTAINERID = ").append(":containerid");
		
		Query query = sessionFactory.getCurrentSession().createSQLQuery(queryForSelectData.toString()).addEntity(Shippingbill.class)
											.setParameter("sbid", sbid).setParameter("containerid", containerid);
		
		shippingBillList = query.list();
		
		if(shippingBillList != null && !shippingBillList.isEmpty()){
			return shippingBillList.get(0);
		} else {
			return null;
		}
		
	}
	
	/**
	 * This method check whether ShppingBill number and cotainerID is exist with association or not.
	 * 
	 * @param sbillno
	 * @param containerid
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Transactional
	public Boolean checkForExistShippingBillNumberAndContainer(Serializable sbillno,Serializable containerid){
		
		StringBuilder queryForSelectData = new StringBuilder();
		
		queryForSelectData.append(" SELECT * FROM SHIPPINGBILL SB ");
		queryForSelectData.append(" INNER JOIN SHIPPINGCONTAINERS SC ON SB.SBID = SC.SBID ");
		queryForSelectData.append(" WHERE SB.SBILLNO = ").append(":sbillno").append(" AND SC.CONTAINERID = ").append(":containerid");
		
		Query query = sessionFactory.getCurrentSession().createSQLQuery(queryForSelectData.toString()).addEntity(Shippingbill.class)
											.setParameter("sbillno", sbillno).setParameter("containerid", containerid);
		
		List<Shippingbill> shippingBillList = query.list();
		
		if(shippingBillList != null && !shippingBillList.isEmpty()){
			
			return Boolean.TRUE;
		
		} else {
			
			return Boolean.FALSE;
		}
		
	}
	
	/**
	 * This method populate ShippingBill Details
	 * 
	 * @param sbillno
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Transactional
	public List<Shippingbill> populateShippingBill(String sbillno){
		
		Integer sbillnoInteger = null;
		List<Shippingbill> shippingBillList = new ArrayList<Shippingbill>();
		
		if(sbillno instanceof String){
			sbillnoInteger = Integer.parseInt((String) sbillno);
		}
		
		StringBuilder queryForSelectData = new StringBuilder();
		
		queryForSelectData.append(" SELECT * FROM SHIPPINGBILL ");
		queryForSelectData.append(" WHERE 1=1 ");
		queryForSelectData.append(" AND SBILLNO LIKE '%").append(sbillnoInteger).append("%' ");
		
		Query query = sessionFactory.getCurrentSession().createSQLQuery(queryForSelectData.toString()).addEntity(Shippingbill.class);
		
		shippingBillList = query.list();
		
		return shippingBillList;
	}
}
